package com.xxl.job.executor.service.impl;

import com.alibaba.fastjson.JSON;
import com.mongodb.BasicDBObject;
import com.mongodb.DBObject;
import com.xxl.job.core.biz.model.ReturnT;
import com.xxl.job.core.constants.Constants;
import com.xxl.job.core.enums.WebReportNames;
import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.core.util.DateUtil;
import com.xxl.job.core.util.ListUtils;
import com.xxl.job.executor.dao.WebReportHistoryMapper;
import com.xxl.job.executor.dao.WebReportMapper;
import com.xxl.job.executor.model.WebReport;
import com.xxl.job.executor.model.WebReportHistory;
import com.xxl.job.executor.model.WebReportRank;
import com.xxl.job.executor.service.WebReportService;
import com.xxl.job.executor.service.business.BaseService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.data.domain.Sort;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationResults;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.AbstractDriverBasedDataSource;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.util.*;

//@Service
public class WebReportServiceImpl extends BaseService implements WebReportService {
    private Logger logger = LoggerFactory.getLogger(WebReportServiceImpl.class);

    @Resource(name = "sharedJdbcTemplate")
    private JdbcTemplate sharedJdbcTemplate;

    @Resource(name="sharedMongoTemplate")
    private MongoTemplate sharedMongoTemplate;

    @Autowired(required = false)
    private WebReportMapper webReportMapper;

    @Autowired(required = false)
    private WebReportHistoryMapper webReportHistoryMapper;

    @Value("${Spring.data.mongodb.shared.host}")
    private String mongodbUrl;

    @Override
    public ReturnT<Integer> save(WebReport webReport) {
        int result = webReportMapper.save(webReport);
        if(result > 0) {
            return new ReturnT<>(result);
        } else {
            return new ReturnT<>(ReturnT.FAIL_CODE, "保存失败");
        }
    }

    @Override
    public ReturnT<Integer> delete(int id) {
        int result = webReportMapper.delete(id);
        if(result > 0) {
            return new ReturnT<>(result);
        } else {
            return new ReturnT<>(ReturnT.FAIL_CODE, "删除失败");
        }
    }

    @Override
    public ReturnT<Integer> update(WebReport webReport) {
        int result = webReportMapper.update(webReport);
        if(result > 0) {
            return new ReturnT<>(result);
        } else {
            return new ReturnT<>(ReturnT.FAIL_CODE, "更新失败");
        }
    }

    @Override
    public ReturnT<WebReport> loadById(int id) {
        WebReport result = webReportMapper.loadById(id);
        if(result != null) {
            return new ReturnT<>(result);
        } else {
            return new ReturnT<>(ReturnT.FAIL_CODE, "查询失败");
        }
    }

    /**
     * 数据共享统计
     * @return
     */
    @Override
    public ReturnT<String> collection() {
        ReturnT<String> r1 = collection_total(Constants.APPLICATION_TOTAL);// 应用总计
        ReturnT<String> r2 = collection_total(Constants.SUBSCRIBE_TOTAL);// 订阅服务总计
        ReturnT<String> r3 = collection_total_mongodb(Constants.REQUEST_TOTAL);//服务调用总计
        ReturnT<String> r4 = collection_total(Constants.SERVICE_TOTAL);// 共享服务总计
        ReturnT<String> r5 = collection_total(Constants.DEPARTMENT_TOTAL);// 部门总计
        //应用调用服务排名
        ReturnT<String> r6 = collection_rank_mongodb1(Constants.APPLICATION_REQUEST_RANK, Constants.APPLICATION_REQUEST_RANK_TOPN);
        //应用订阅服务排名
        ReturnT<String> r7 = collection_rank_jdbc(Constants.APPLICATION_SUBSCRIBE_RANK, Constants.APPLICATION_SUBSCRIBE_RANK_TOPN);
        //部门订阅服务排名
        ReturnT<String> r8 = collection_rank_jdbc(Constants.DEPARTMENT_SUBSCRIBE_RANK, Constants.DEPARTMENT_SUBSCRIBE_RANK_TOPN);
        //服务授权应用排名
        ReturnT<String> r9 = collection_rank_jdbc(Constants.AUTHORIZE_APPLICATION_RANK, Constants.AUTHORIZE_APPLICATION_RANK_TOPN);
        //部门调用服务排名
        ReturnT<String> r10 = collection_rank_mongodb2(Constants.DEPARTMENT_REQUEST_RANK, Constants.DEPARTMENT_REQUEST_RANK_TOPN);

        if(ReturnT.isFail(r1) || ReturnT.isFail(r2) || ReturnT.isFail(r3) ||
                ReturnT.isFail(r4) || ReturnT.isFail(r5) || ReturnT.isFail(r6) ||
                ReturnT.isFail(r7) || ReturnT.isFail(r8) || ReturnT.isFail(r9) ||
                ReturnT.isFail(r10)) {
            return ReturnT.FAIL;
        } else {
            return ReturnT.SUCCESS;
        }

    }

    /**
     * 应用总计,订阅服务总计,服务调用总计,共享服务总计,部门总计
     * @return
     */
    private ReturnT<String> collection_total(String name) {
        try {
            String query = getQuerySql(name);//获取对应的sql
            Object result = sharedJdbcTemplate.queryForObject(query, Object.class);//查询
            if(result != null) {
                WebReport webReport;
                Map<String, String> map = new HashMap<>();
                map.put("model", Constants.WEB_REPORT_MODEL);
                map.put("name", name);
                map.put("key", name);
                List<WebReport> webReports = webReportMapper.query(map);//查询原本的记录
                if(ListUtils.isNotEmpty(webReports)) {//存在
                    webReport = webReports.get(0);
                    webReportMapper.delete(webReport.getId());
                }
                webReport = new WebReport();
                webReport.setModel(Constants.WEB_REPORT_MODEL);
                webReport.setCreateTime(DateUtil.formatDateTime(new Date()));
                webReport.setCron("");
                AbstractDriverBasedDataSource dataSource = (AbstractDriverBasedDataSource) sharedJdbcTemplate.getDataSource();
                webReport.setDataSource(dataSource.getUrl());
                webReport.setKey(name);
                webReport.setValue(result.toString());
                webReport.setName(name);
                webReport.setRemarks(WebReportNames.getRemarkByName(name));
                webReport.setType(Constants.WEB_REPORT_TYPE1);
                webReportMapper.save(webReport);//保存并获取返回的id

                WebReportHistory webReportHistory = new WebReportHistory();
                webReportHistory.setId(webReport.getId());
                webReportHistory.setModel(webReport.getModel());
                webReportHistory.setName(webReport.getName());
                webReportHistory.setKey(webReport.getKey());
                webReportHistory.setType(webReport.getType());
                webReportHistory.setValue(webReport.getValue());
                webReportHistory.setCron(webReport.getCron());
                webReportHistory.setCreateTime(webReport.getCreateTime());
                webReportHistory.setDataSource(webReport.getDataSource());
                webReportHistory.setRemarks(webReport.getRemarks());
                webReportHistoryMapper.save(webReportHistory);//备份历史记录

                XxlJobLogger.log("XXL-JOB, WebReportJobHandler " + WebReportNames.getRemarkByName(name) + "成功");
                XxlJobLogger.log("结果：{}", JSON.toJSON(webReport));
            } else {
                logger.info(WebReportNames.getRemarkByName(name) + ":查询数据不存在");
                XxlJobLogger.log(WebReportNames.getRemarkByName(name) + ":查询数据不存在");
            }
            return new ReturnT<>(result.toString());
        } catch (Exception e) {
            XxlJobLogger.log("XXL-JOB, WebReportJobHandler" + WebReportNames.getRemarkByName(name) + "失败");
            return new ReturnT<>(ReturnT.FAIL_CODE, "查询失败");
        }
    }

    /**
     * 服务调用总计
     * @param name
     * @return
     */
    private ReturnT<String> collection_total_mongodb(String name) {
        try {
            Query query = new Query(Criteria.where("status").is(200));//区分数据类型，用字符串的200，查询不出来
//            query.with(new Sort(Sort.Direction.ASC, "age").
//                    and(new Sort(Sort.Direction.DESC, "date")));
            Long count = sharedMongoTemplate.count(query, "service_log");
            if(count != null) {
                WebReport webReport;
                Map<String, String> map = new HashMap<>();
                map.put("model", Constants.WEB_REPORT_MODEL);
                map.put("name", name);
                map.put("key", name);
                List<WebReport> webReportList = webReportMapper.query(map);//查询原本的记录
                if(ListUtils.isNotEmpty(webReportList)) {//删除原本的统计记录
                    webReport = webReportList.get(0);
                    webReportMapper.delete(webReport.getId());
                }
                webReport = new WebReport();
                webReport.setModel(Constants.WEB_REPORT_MODEL);
                webReport.setCreateTime(DateUtil.formatDateTime(new Date()));
                webReport.setCron("");
                webReport.setDataSource(mongodbUrl);
                webReport.setKey(name);
                webReport.setValue(count.toString());
                webReport.setName(name);
                webReport.setRemarks(WebReportNames.getRemarkByName(name));
                webReport.setType(Constants.WEB_REPORT_TYPE1);
                webReportMapper.save(webReport);//保存并获取返回的id

                WebReportHistory webReportHistory = new WebReportHistory();
                webReportHistory.setId(webReport.getId());
                webReportHistory.setModel(webReport.getModel());
                webReportHistory.setName(webReport.getName());
                webReportHistory.setKey(webReport.getKey());
                webReportHistory.setType(webReport.getType());
                webReportHistory.setValue(webReport.getValue());
                webReportHistory.setCron(webReport.getCron());
                webReportHistory.setCreateTime(webReport.getCreateTime());
                webReportHistory.setDataSource(webReport.getDataSource());
                webReportHistory.setRemarks(webReport.getRemarks());
                webReportHistoryMapper.save(webReportHistory);//备份历史记录
                XxlJobLogger.log("XXL-JOB, WebReportJobHandler " + WebReportNames.getRemarkByName(name) + "成功");
                XxlJobLogger.log("结果：{}", JSON.toJSON(webReport));
            } else {
                logger.info(WebReportNames.getRemarkByName(name) + ":查询数据不存在");
                XxlJobLogger.log(WebReportNames.getRemarkByName(name) + ":查询数据不存在");
            }

            System.out.println("测试"+WebReportNames.getRemarkByName(name)+"：" + count.toString());
            return new ReturnT<>(count.toString());
        } catch (Exception e) {
            XxlJobLogger.log("XXL-JOB, WebReportJobHandler " + WebReportNames.getRemarkByName(name) + "失败");
            return new ReturnT<>(ReturnT.FAIL_CODE, "查询失败");
        }
    }

    /**
     * 应用调用服务排名TOP3
     * @param name
     * @return
     */
    private ReturnT<String> collection_rank_mongodb1(String name, int topN) {
        try {
            Aggregation aggregation = Aggregation.newAggregation(
                    Aggregation.match(Criteria.where("application_id").ne("-").and("status").is(200)),
                    Aggregation.group("application_id").count().as("count"),
                    Aggregation.sort(new Sort(Sort.Direction.ASC, "count"))
            );
            // 按application_id分组查询，取前三
            AggregationResults<BasicDBObject> aggregationResults =
                    sharedMongoTemplate.aggregate(aggregation, "service_log", BasicDBObject.class);

            List<DBObject> list = getMongoTopN(aggregationResults, 3);//取前三

            System.out.println("测试"+WebReportNames.getRemarkByName(name)+":" + com.alibaba.fastjson.JSON.toJSONString(list));

            List<WebReportRank> webReportRankList = new ArrayList<>();
            if(ListUtils.isNotEmpty(list)) {
                int currentI = 0;
                int index = 1;
                for (int i = 0; i < list.size() && list.size() >= currentI; i++) {
                    if(index > topN) {
                        break;
                    }
                    currentI = i + 1;
                    WebReportRank webReportRank = new WebReportRank();
                    webReportRank.setType(name);
                    webReportRank.setIndex(index);
                    webReportRank.setCount((int)list.get(i).get("count"));
                    Map<String, Object> map;
                    Object object = list.get(i).get("_id");
                    String application_id = object.toString();
                    map = sharedJdbcTemplate.queryForMap("select application_name from g_application_info where id = '" + application_id + "'");
                    if(!CollectionUtils.isEmpty(map)) {
                        webReportRank.setName(String.valueOf(map.get("application_name")));
                        webReportRankList.add(webReportRank);
                        index += 1;
                    }
                }
            }

            if(ListUtils.isNotEmpty(webReportRankList)) {
                WebReport webReport;
                Map<String, String> map = new HashMap<>();
                map.put("model", Constants.WEB_REPORT_MODEL);
                map.put("name", name);
                map.put("key", name);
                List<WebReport> webReportList = webReportMapper.query(map);//查询原本的记录
                if(ListUtils.isNotEmpty(webReportList)) {//删除原本的统计记录
                    webReport = webReportList.get(0);
                    webReportMapper.delete(webReport.getId());
                }
                webReport = new WebReport();
                webReport.setModel(Constants.WEB_REPORT_MODEL);
                webReport.setCreateTime(DateUtil.formatDateTime(new Date()));
                webReport.setCron("");
                webReport.setDataSource(mongodbUrl);
                webReport.setKey(name);
                webReport.setValue(com.alibaba.fastjson.JSON.toJSONString(webReportRankList));
                webReport.setName(name);
                webReport.setRemarks(WebReportNames.getRemarkByName(name));
                webReport.setType(Constants.WEB_REPORT_TYPE2);
                webReportMapper.save(webReport);//保存并获取返回的id

                WebReportHistory webReportHistory = new WebReportHistory();
                webReportHistory.setId(webReport.getId());
                webReportHistory.setModel(webReport.getModel());
                webReportHistory.setName(webReport.getName());
                webReportHistory.setKey(webReport.getKey());
                webReportHistory.setType(webReport.getType());
                webReportHistory.setValue(webReport.getValue());
                webReportHistory.setCron(webReport.getCron());
                webReportHistory.setCreateTime(webReport.getCreateTime());
                webReportHistory.setDataSource(webReport.getDataSource());
                webReportHistory.setRemarks(webReport.getRemarks());
                webReportHistoryMapper.save(webReportHistory);//备份历史记录
                XxlJobLogger.log("XXL-JOB, WebReportJobHandler " + WebReportNames.getRemarkByName(name) + "成功");
                XxlJobLogger.log("结果：{}", JSON.toJSON(webReport));
            } else {
                logger.info(WebReportNames.getRemarkByName(name) + ":查询数据不存在");
                XxlJobLogger.log(WebReportNames.getRemarkByName(name) + ":查询数据不存在");
            }

            System.out.println("测试"+WebReportNames.getRemarkByName(name)+":" + com.alibaba.fastjson.JSON.toJSONString(webReportRankList));

            return new ReturnT<>(com.alibaba.fastjson.JSON.toJSONString(webReportRankList));
        } catch (Exception e) {
            XxlJobLogger.log("XXL-JOB, WebReportJobHandler " + WebReportNames.getRemarkByName(name) + "失败");
            return new ReturnT<>(ReturnT.FAIL_CODE, "查询失败");
        }
    }

    /**
     * 部门调用服务排名
     * @param name
     * @return
     */
    private ReturnT<String> collection_rank_mongodb2(String name, int topN) {
        try {
            Aggregation aggregation = Aggregation.newAggregation(
                    Aggregation.match(Criteria.where("application_id").ne("-").and("status").is(200)),
                    Aggregation.group("application_id").count().as("count")
            );
            // 按application_id分组查询，再关联查询对应的部门
            AggregationResults<BasicDBObject> aggregationResults =
                    sharedMongoTemplate.aggregate(aggregation, "service_log", BasicDBObject.class);

            List<DBObject> list = new ArrayList<>();
            for (Iterator<BasicDBObject> iterator = aggregationResults.iterator(); iterator.hasNext(); ) {
                DBObject obj = iterator.next();
                String application_id = obj.get("_id").toString();
                String query = "select b.campany_name from " +
                        "(select * from g_application_info where is_del="+Constants.IS_NOT_DEL +
                        " and is_enable="+Constants.ENABLE+" and is_approve="+Constants.APPROVE_STATUS_2+")a " +
                        " left join (select * from s_user where approve_type=1 and preset_type=0 and is_del="+Constants.IS_NOT_DEL +
                        " and is_enable ="+Constants.ENABLE+")b on a.create_id = b.id " +
                        //" where a.id = '"+application_id+"';";
                        " where b.campany_name<>'无' and a.id = '"+application_id+"';";
                //System.out.println(query);
                try {
                    Object object = sharedJdbcTemplate.queryForObject(query, Object.class);
                    if (object != null) {
                        String company_name = object.toString();
                        if (ListUtils.isNotEmpty(list)) {
                            for (DBObject dbObject : list) {
                                if (company_name.equals(dbObject.get("_id").toString())) {
                                    dbObject.put("count", Integer.valueOf(dbObject.get("count").toString()) + Integer.valueOf(obj.get("count").toString()));
                                    break;
                                }
                            }
                        } else {
                            obj.put("_id", company_name);
                            list.add(obj);
                        }
                    }
                } catch (EmptyResultDataAccessException e) {
                    continue;
                }
            }

            System.out.println("测试"+WebReportNames.getRemarkByName(name)+":" + com.alibaba.fastjson.JSON.toJSONString(list));

            List<WebReportRank> webReportRankList = new ArrayList<>();
            if(ListUtils.isNotEmpty(list)) {
                list = sortByDes(list);

                int currentI = 0;
                int index = 1;
                for (int i = 0; i < list.size() && list.size() >= currentI; i++) {
                    if(index > topN) {
                        break;
                    }
                    currentI = i + 1;
                    WebReportRank webReportRank = new WebReportRank();
                    webReportRank.setType(name);
                    webReportRank.setIndex(index);
                    DBObject dbObject = list.get(i);
                    if(dbObject != null) {
                        webReportRank.setCount((int)dbObject.get("count"));
                        webReportRank.setName(String.valueOf(dbObject.get("_id")));
                        webReportRankList.add(webReportRank);
                        index += 1;
                    }
                }
            }

            System.out.println("测试"+WebReportNames.getRemarkByName(name)+":" + com.alibaba.fastjson.JSON.toJSONString(webReportRankList));

            if(ListUtils.isNotEmpty(webReportRankList)) {
                WebReport webReport;
                Map<String, String> map = new HashMap<>();
                map.put("model", Constants.WEB_REPORT_MODEL);
                map.put("name", name);
                map.put("key", name);
                List<WebReport> webReportList = webReportMapper.query(map);//查询原本的记录
                if(ListUtils.isNotEmpty(webReportList)) {//删除原本的统计记录
                    webReport = webReportList.get(0);
                    webReportMapper.delete(webReport.getId());
                }
                webReport = new WebReport();
                webReport.setModel(Constants.WEB_REPORT_MODEL);
                webReport.setCreateTime(DateUtil.formatDateTime(new Date()));
                webReport.setCron("");
                webReport.setDataSource(mongodbUrl);
                webReport.setKey(name);
                webReport.setValue(com.alibaba.fastjson.JSON.toJSONString(webReportRankList));
                webReport.setName(name);
                webReport.setRemarks(WebReportNames.getRemarkByName(name));
                webReport.setType(Constants.WEB_REPORT_TYPE2);
                webReportMapper.save(webReport);//保存并获取返回的id

                WebReportHistory webReportHistory = new WebReportHistory();
                webReportHistory.setId(webReport.getId());
                webReportHistory.setModel(webReport.getModel());
                webReportHistory.setName(webReport.getName());
                webReportHistory.setKey(webReport.getKey());
                webReportHistory.setType(webReport.getType());
                webReportHistory.setValue(webReport.getValue());
                webReportHistory.setCron(webReport.getCron());
                webReportHistory.setCreateTime(webReport.getCreateTime());
                webReportHistory.setDataSource(webReport.getDataSource());
                webReportHistory.setRemarks(webReport.getRemarks());
                webReportHistoryMapper.save(webReportHistory);//备份历史记录
                XxlJobLogger.log("XXL-JOB, WebReportJobHandler " + WebReportNames.getRemarkByName(name) + "成功");
                XxlJobLogger.log("结果：{}", JSON.toJSON(webReport));
            } else {
                logger.info(WebReportNames.getRemarkByName(name) + ":查询数据不存在");
                XxlJobLogger.log(WebReportNames.getRemarkByName(name) + ":查询数据不存在");
            }

            return new ReturnT<>(com.alibaba.fastjson.JSON.toJSONString(webReportRankList));
        } catch (Exception e) {
            XxlJobLogger.log("XXL-JOB, WebReportJobHandler " + WebReportNames.getRemarkByName(name) + "失败");
            return new ReturnT<>(ReturnT.FAIL_CODE, "查询失败");
        }
    }

    /**
     * 应用订阅服务排名  部门订阅服务排名  服务授权应用排名
     * @param name 排名名称
     * @param topN 排名前N位
     * @return
     */
    private ReturnT<String> collection_rank_jdbc(String name, int topN) {
        try {
            String query = getQuerySql(name);//获取对应的sql
            List<Map<String, Object>> list = sharedJdbcTemplate.queryForList(query);//查询
            System.out.println("测试"+WebReportNames.getRemarkByName(name)+":" + JSON.toJSONString(list));

            List<WebReportRank> webReportRankList = new ArrayList<>();
            if(ListUtils.isNotEmpty(list)) {
                int currentI = 0;
                int index = 1;
                for (int i = 0; i < list.size() && list.size() >= currentI; i++) {
                    if(index > topN) {
                        break;
                    }
                    currentI = i + 1;
                    WebReportRank webReportRank = new WebReportRank();
                    webReportRank.setType(name);
                    webReportRank.setIndex(index);
                    Map<String, Object> map = list.get(i);
                    if(!CollectionUtils.isEmpty(map)) {
                        webReportRank.setCount(Integer.parseInt(map.get("count").toString()));
                        webReportRank.setName(map.get("name").toString());
                        webReportRankList.add(webReportRank);
                        index += 1;
                    }
                }
            }

            if(ListUtils.isNotEmpty(webReportRankList)) {
                WebReport webReport;
                Map<String, String> map = new HashMap<>();
                map.put("model", Constants.WEB_REPORT_MODEL);
                map.put("name", name);
                map.put("key", name);
                List<WebReport> webReportList = webReportMapper.query(map);//查询原本的记录
                if(ListUtils.isNotEmpty(webReportList)) {//删除原本的统计记录
                    webReport = webReportList.get(0);
                    webReportMapper.delete(webReport.getId());
                }
                webReport = new WebReport();
                webReport.setModel(Constants.WEB_REPORT_MODEL);
                webReport.setCreateTime(DateUtil.formatDateTime(new Date()));
                webReport.setCron("");
                AbstractDriverBasedDataSource dataSource = (AbstractDriverBasedDataSource) sharedJdbcTemplate.getDataSource();
                webReport.setDataSource(dataSource.getUrl());
                webReport.setKey(name);
                webReport.setValue(JSON.toJSONString(webReportRankList));
                webReport.setName(name);
                webReport.setRemarks(WebReportNames.getRemarkByName(name));
                webReport.setType(Constants.WEB_REPORT_TYPE2);
                webReportMapper.save(webReport);//保存并获取返回的id

                WebReportHistory webReportHistory = new WebReportHistory();
                webReportHistory.setId(webReport.getId());
                webReportHistory.setModel(webReport.getModel());
                webReportHistory.setName(webReport.getName());
                webReportHistory.setKey(webReport.getKey());
                webReportHistory.setType(webReport.getType());
                webReportHistory.setValue(webReport.getValue());
                webReportHistory.setCron(webReport.getCron());
                webReportHistory.setCreateTime(webReport.getCreateTime());
                webReportHistory.setDataSource(webReport.getDataSource());
                webReportHistory.setRemarks(webReport.getRemarks());
                webReportHistoryMapper.save(webReportHistory);//备份历史记录
                XxlJobLogger.log("XXL-JOB, WebReportJobHandler " + WebReportNames.getRemarkByName(name) + "成功");
                XxlJobLogger.log("结果：{}", JSON.toJSON(webReport));
            } else {
                logger.info(WebReportNames.getRemarkByName(name) + ":查询数据不存在");
                XxlJobLogger.log(WebReportNames.getRemarkByName(name) + ":查询数据不存在");
            }

            System.out.println("测试"+WebReportNames.getRemarkByName(name)+"：" + JSON.toJSONString(webReportRankList));
            return new ReturnT<>(JSON.toJSONString(webReportRankList));
        } catch (Exception e) {
            XxlJobLogger.log("XXL-JOB, WebReportJobHandler " + WebReportNames.getRemarkByName(name) + "失败");
            return new ReturnT<>(ReturnT.FAIL_CODE, "查询失败");
        }
    }

    /**
     * 获取对应的sql语句
     * @param name
     * @return
     */
    private String getQuerySql(String name) {
        String query = "";

        switch (name) {
            case Constants.APPLICATION_TOTAL: // 应用总计（未删除 已启用 is_approve最终是审核通过，状态值为2）
                query = "select count(*) as num from g_application_info where is_del = " +
                        Constants.IS_NOT_DEL + " and is_enable = " + Constants.ENABLE +
                        " and is_approve = " + Constants.APPROVE_STATUS_2;
                break;
            case Constants.SUBSCRIBE_TOTAL:// 订阅服务总计
                query = "select count(*) from s_order where is_del = " +
                        Constants.IS_NOT_DEL + " and status = " + Constants.SUBSCRIBE_SUCCESS;
                break;
            case Constants.SERVICE_TOTAL:// 共享服务总计(approve_type最终是发布成功，状态值为5)
                query = "select count(*) from g_service_info where is_del = " +
                        Constants.IS_NOT_DEL + " and status = " + Constants.ENABLE +
                        " and approve_type = " + Constants.APPROVE_STATUS_5;
                break;
            case Constants.DEPARTMENT_TOTAL:// 部门总计（审批通过 预设类型：非预设 未删除 已启用）
                query = "select count(*) from (select t.campany_name from s_user t " +
                        "where t.campany_name <> '无' and approve_type = 1 and preset_type = 0 " +
                        "and is_del = " + Constants.IS_NOT_DEL + " and is_enable = " + Constants.ENABLE +
                        " group by t.campany_name) as s; ";
                break;
            case Constants.APPLICATION_SUBSCRIBE_RANK:// 应用订阅服务排名(授权类型：订阅，3代表已经订阅成功 审批通过:一般都是2 未删除) // g_application_service表按application_id分组 审批通过 未删除； g_application_info未删除 已启用 审批通过
                query = "select a.count, b.application_name as `name` from (select count(*) count,application_id from g_application_service " +
                        "where approve_type = 2 and is_del = " + Constants.IS_NOT_DEL + " group by application_id)a " +
                        "left join (select * from g_application_info where is_del = " + Constants.IS_NOT_DEL +
                        " and is_enable = " + Constants.ENABLE + " and is_approve = " + Constants.APPROVE_STATUS_2 +
                        ")b on a.application_id = b.id order by a.count desc";
                break;
            case Constants.DEPARTMENT_SUBSCRIBE_RANK:// 部门订阅服务排名(s_order的customer_id是下单人id)
                query = "select a.count, b.`name` from (select count(*) count, customer_id from s_order where is_del = " + Constants.IS_NOT_DEL +
                        " and `status` = " + Constants.SUBSCRIBE_SUCCESS + " GROUP BY customer_id) a " +
                        "left join (select campany_name as `name`,id from s_user where approve_type = 1 and preset_type = 0 and is_del = " + Constants.IS_NOT_DEL +
                        " and is_enable = " + Constants.ENABLE + ")b on a.customer_id = b.id;";
                break;
            case Constants.AUTHORIZE_APPLICATION_RANK:// 服务授权应用排名(g_application_service表按service_id分组 审批通过 未删除； g_service_info已启用)
                query = "select a.count, b.`name` from (select count(*) count,service_id from g_application_service " +
                        "where approve_type = 2 and is_del = " + Constants.IS_NOT_DEL + " group by service_id)a " +
                        "left join (select `name`,`id` from g_service_info where `status`=1 )b on a.service_id = b.id order by a.count desc";
                break;
        }
        return query;
    }
}
